package com.cloudpivot.receivales.crm.repository.weekly;

import com.cloudpivot.receivales.crm.entity.weekly.WeeklyCount;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface WeeklyCountRepository extends JpaRepository<WeeklyCount, String> {

    /**
     * 获取周报 统计报表
     * @param parentBusinessUnitId
     * @return
     */
    @Query(nativeQuery = true, value = "select tmp.bunitid as BunitId,tmp.UnitName,tmp.LastName,tmp.SystemUserId,count(tmp.SystemUserId) as WeeklyCounts from (select bunit.BusinessUnitId as bunitid,bunit.name as unitName,sysuser.LastName,sysuser.SystemUserId,weekly.CreatedOn from SystemUserBase sysuser inner join BusinessUnitBase bunit on  bunit.ParentBusinessUnitId=?1 and bunit.IsDisabled=0 and sysuser.BusinessUnitId=bunit.BusinessUnitId inner join new_weekly_reports weekly on weekly.OwnerId=sysuser.SystemUserId where weekly.CreatedOn between ?2 and ?3 ) tmp group by tmp.unitName,tmp.LastName,tmp.SystemUserId,tmp.bunitid")
    List<WeeklyCount> getLists(String parentBusinessUnitId,String start,String end);
}
